/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.webaccess.retailws.db.DBConnection;
import java.util.*;
import com.webaccess.retailws.Bean.CategoryBean;
import java.util.ArrayList;

/**
 *
 * @author Owner
 */
public class CategoryDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    ResultSet rsCategory = null;
    String result = null;
    StringBuffer myQuery = null;
    List categList = new ArrayList();
    CommonDAO commDAO = null;
    CategoryBean categBean = null;

    public CategoryDAO() {
        try {
            dbc = new DBConnection();
            //conn = (Connection) dbc.getConnection();

            commDAO = new CommonDAO();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public LinkedHashMap getCategoryLHMap(int i) {
        LinkedHashMap categoryLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        if (i == 0) {
            myQuery.append("SELECT 	`category_id`,`category` FROM `retailws`.`category` WHERE parentcategory_id = category_id");
        } else {
            myQuery.append("SELECT 	`category_id`,`category` FROM `retailws`.`category` WHERE parentcategory_id = '");
            myQuery.append(i);
            myQuery.append("'");
        }
        categoryLHMap = commDAO.getData(myQuery.toString());
        return categoryLHMap;
    }

    public CategoryBean[] getAllCategory() {
        CategoryBean[] categBeanList = null;
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectcategory('0')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBeanList = setAllCategory(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBeanList;
        }

    }

    private CategoryBean[] setAllCategory(ResultSet rs) {
        List alCategory = new ArrayList();
        try {
            alCategory.clear();
            while (rs.next()) {
                categBean = new CategoryBean();
                categBean.setCategoryId(rs.getLong("category_id"));
                categBean.setCategoryName(rs.getString("category"));
                categBean.setParentCategory(rs.getString("parentcategory"));
                categBean.setCategoryStatusId(rs.getLong("status_id"));
                categBean.setCategoryStatus(rs.getString("status"));
                categBean.setCategoryDesc(rs.getString("description"));
                categBean.setCategoryTitle(rs.getString("title"));
                categBean.setCategoryImageName(rs.getString("image"));
                alCategory.add(categBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (CategoryBean[]) alCategory.toArray(new CategoryBean[0]);
        }
    }

    public CategoryBean getParentCategoryList() {
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT `category_id`,`category` FROM `retailws`.`category` WHERE parentcategory_id = category_id");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBean = setParentCategoryList(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBean;
        }
    }

    private CategoryBean setParentCategoryList(ResultSet rs) {
        try {
            while (rs.next()) {
                categBean = new CategoryBean();
                categBean.setCategoryId(rs.getLong("category_id"));
                categBean.setCategoryName(rs.getString("category"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return categBean;
        }
    }

    public CategoryBean getSubCategoryList(Long categoryId) {
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed() ) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT `category_id`,`category` FROM `retailws`.`category` WHERE parentcategory_id = '");
            myQuery.append(categoryId);
            myQuery.append("'");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBean = setParentCategoryList(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBean;
        }
    }

    public CategoryBean getCategory(Long categoryId) {
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectcategory('");
            myQuery.append(categoryId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBean = setCategory(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBean;
        }
    }

    private CategoryBean setCategory(ResultSet rs) {
        try {
            while (rs.next()) {
                categBean = new CategoryBean();
                categBean.setCategoryId(rs.getLong("category_id"));
                categBean.setCategoryName(rs.getString("category"));
                categBean.setParentCategory(rs.getString("parentcategory"));
                categBean.setParentCategoryId(rs.getLong("parentcategory_id"));
                categBean.setCategoryStatusId(rs.getLong("status_id"));
                categBean.setCategoryStatus(rs.getString("status"));
                categBean.setCategoryDesc(rs.getString("description"));
                categBean.setCategoryImageName(rs.getString("image"));
                categBean.setCategoryTitle(rs.getString("title"));
                if (categBean.getCategoryId() == categBean.getParentCategoryId()) {
                    categBean.setMainCategory(1);
                } else {
                    categBean.setMainCategory(0);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return categBean;
        }
    }

    public LinkedHashMap getSubCategoryLHMap() {
        LinkedHashMap subCategoryLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
//        myQuery.append("SELECT `category_id`,`category` FROM `retailws`.`category` WHERE `category_id` = ");
//        myQuery.append("(SELECT `parentcategory_id` FROM `retailws`.`category` WHERE `category_id` = '");
//        myQuery.append(categoryId);
//        myQuery.append("')");

        myQuery.append("SELECT 	`category_id`,`category` FROM `retailws`.`category` WHERE parentcategory_id = category_id");
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                while (rs.next()) {
                    subCategoryLHMap.put(rs.getString(1), rs.getString(2));
                    myQuery = new StringBuffer();
                    myQuery.append("SELECT 	`category_id`,`category`,`parentcategory_id` FROM `retailws`.`category` WHERE parentcategory_id = '");
                    myQuery.append(rs.getString("category_id"));
                    myQuery.append("'");
                    rsCategory = dbc.executeQuery(conn, myQuery.toString());
                    if (rsCategory != null) {
                        String rsValue = String.valueOf(rsCategory);
                        if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                            while (rsCategory.next()) {
                                subCategoryLHMap.put(rsCategory.getString(1), rs.getString(2) + "->" + rsCategory.getString(2));
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rsCategory);
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return subCategoryLHMap;
        }
    }

    public boolean saveCategory(CategoryBean categBean) {
        boolean isCategorySaved = false;
        Long categoryId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            if (categBean.getCategoryId() == null) {
                myQuery.append("CALL sp_savecategory(NULL,'");
            } else {
                myQuery.append("CALL sp_savecategory('");
                myQuery.append(categBean.getCategoryId());
                myQuery.append("','");
            }
            myQuery.append(categBean.getCategoryName());
            myQuery.append("','");
            myQuery.append(categBean.getCategoryDesc());
            myQuery.append("',");
            if (categBean.getMainCategory() == 0) {
                myQuery.append("'");
                myQuery.append(categBean.getParentCategoryId());
                myQuery.append("','");
            } else {
                myQuery.append("'0','");
            }
            myQuery.append(categBean.getCategoryImageName());
            myQuery.append("','");
            myQuery.append(categBean.getCategoryTitle());
            myQuery.append("','");
            myQuery.append(categBean.getCategoryStatusId());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (categBean.getCategoryId() == null) {
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            categoryId = rs.getLong("sp_CategoryId");
                        }
                    }
                }
                if (categoryId > 0) {
                    isCategorySaved = true;
                }
            } else {
                isCategorySaved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isCategorySaved;
        }
    }

    public boolean deleteCategory(Long categoryId) {
        boolean isCategoryDeleted = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deletecategory('");
            myQuery.append(categoryId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isCategoryDeleted = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isCategoryDeleted;
        }
    }

    public CategoryBean[] searchCategory(Long parentCategoryId) {
        CategoryBean[] categBeanList = null;
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchCategory('");
            myQuery.append(parentCategoryId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBeanList = setAllCategory(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBeanList;
        }
    }

    public CategoryBean[] getMainCategoryList() {
        CategoryBean[] categBeanList = null;
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed() ) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL `sp_selectparentcategory`()");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBeanList = setMainCategoryList(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBeanList;
        }
    }

    private CategoryBean[] setMainCategoryList(ResultSet rs) {
        List alCategory = new ArrayList();
        try {
            alCategory.clear();
            while (rs.next()) {
                categBean = new CategoryBean();
                categBean.setCategoryId(rs.getLong("category_id"));
                categBean.setCategoryName(rs.getString("category"));
                categBean.setParentCategoryId(rs.getLong("parentcategory_id"));
                categBean.setCategoryStatusId(rs.getLong("status_id"));
                categBean.setCategoryStatus(rs.getString("status"));
                categBean.setCategoryDesc(rs.getString("description"));
                categBean.setCategoryImageName(rs.getString("image"));
                categBean.setCategoryTitle(rs.getString("title"));
                if (categBean.getCategoryId() == categBean.getParentCategoryId()) {
                    categBean.setMainCategory(1);
                } else {
                    categBean.setMainCategory(0);
                }
                alCategory.add(categBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (CategoryBean[]) alCategory.toArray(new CategoryBean[0]);
        }
    }

    public CategoryBean[] getSubCategories(Long categoryId) {
        CategoryBean[] categBeanList = null;
        categBean = new CategoryBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL `sp_selectsubcategory`('");
            myQuery.append(categoryId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    categBeanList = setSubCategories(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return categBeanList;
        }
    }

    private CategoryBean[] setSubCategories(ResultSet rs) {
        List alCategory = new ArrayList();
        try {
            alCategory.clear();
            while (rs.next()) {
                categBean = new CategoryBean();
                categBean.setCategoryId(rs.getLong("category_id"));
                categBean.setCategoryName(rs.getString("category"));
                categBean.setParentCategoryId(rs.getLong("parentcategory_id"));
                categBean.setCategoryStatusId(rs.getLong("status_id"));
                categBean.setCategoryStatus(rs.getString("status"));
                categBean.setCategoryDesc(rs.getString("description"));
                categBean.setCategoryImageName(rs.getString("image"));
                categBean.setCategoryTitle(rs.getString("title"));
                if (categBean.getCategoryId() == categBean.getParentCategoryId()) {
                    categBean.setMainCategory(1);
                } else {
                    categBean.setMainCategory(0);
                }
                alCategory.add(categBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (CategoryBean[]) alCategory.toArray(new CategoryBean[0]);
        }
    }
}
